package com.zzptc.myk.shop.dao;


import com.zzptc.myk.shop.db.DBI;
import com.zzptc.myk.shop.db.DBImpl;
import com.zzptc.myk.shop.vo.Product;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDaoI {
	/**
	 *得到所有商品
	 * @return
	 * @throws SQLException
	 */
	public List<Product> getAllproduct() throws SQLException {
        //1、打开数据库连接
		DBI db = new DBImpl();
		Connection con = db.getConn();
		String sql = "SELECT * FROM taotaobuy_product;";
		//2、发送sql语句
		PreparedStatement ps = con.prepareStatement(sql);
		//3、将数据库中数据放入虚拟表ResultSet中
		ResultSet rs = ps.executeQuery();
		List<Product> list = new ArrayList<Product>();
		//4、将虚拟表中的数据放入集合list中
		while (rs.next()) {
			Product product = new Product();
			product.setTpId(rs.getInt(1));
			product.setTpName(rs.getString(2));
			product.setTpDescription(rs.getString(3));
			product.setTpPrice(rs.getFloat(4));
			product.setTpStock(rs.getInt(5));
			product.setTpcId(rs.getInt(6));
			product.setTpcChildId(rs.getInt(7));
			product.setTpFileName(rs.getString(8));

				list.add(product);
		}
		//4、释放资源
		db.closeAll(null,ps,rs);
		//5、关闭数据库
		db.closeConn();
		return list;
	}

	/**
	 * 新增商品
	 * @param product
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int insert(Product product) throws SQLException {
		return 0;
	}




	//删除商品
	public int detele(int id) throws SQLException {
		DBI db = new DBImpl();
		int flag = 0;
		Connection connection = db.getConn();
		String sql = "DELETE FROM taotaobuy_product WHERE ep_id = "+id;
		PreparedStatement ps = connection.prepareStatement(sql);
		flag = ps.executeUpdate();
		ps.close();
		db.closeConn();
		return flag;
	}

	@Override
	public int update(Product product) throws SQLException {
		//1、打开数据库连接
		DBI db = new DBImpl();
		Connection con = db.getConn();
//		String sql = "UPDATE taotaobuy_product SET ep_name = ? ,epc_id = ? ,"
//				+ " epc_child_id = ? , ep_price = ? , ep_stock = ? WHERE ep_id = "
//				+ product.getEp_id();
//		PreparedStatement ps = conn.prepareStatement(sql);
//		ps.setString(1, product.getEp_name());
//		ps.setInt(2, product.getEpc_id());
//		ps.setInt(3, product.getEpc_child_id());
//		ps.setFloat(4, product.getEp_price());
//		ps.setInt(5, product.getEp_stock());
//		int a = ps.executeUpdate();
//		ps.close();
//		db.closeConn();
		return 0;
	}



	public static void main(String[] args) {
		ProductDaoImpl dao = new ProductDaoImpl();
		int a = 0,id =5;
		/*Product product = new Product();
		product.setEp_id(5);
		product.setEp_name("糖");
		product.setEp_price(564);
		product.setEp_stock(888);
		product.setEpc_id(2);
		product.setEpc_child_id(10);*/
		try {
			//a = dao.detele(id);
			System.out.println(dao.getAllproduct().size());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}


	@Override
	public List<Product> getAllUserFoot(String uId) throws SQLException {
		// TODO Auto-generated method stub
		DBI db = new DBImpl();
		String sql1="select * from taotaobuy_userfoot uf inner join taotaobuy_product p\n" +
				"on uf.tp_id=p.tp_id where uf.to_user_id=? order by tuf_create_time desc limit 20";
		Connection connection = db.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = connection.prepareStatement(sql1);
		ps.setObject(1,uId);
		rs= ps.executeQuery();
		List<Product> list = new ArrayList<Product>();
		while(rs.next()){
			Product product = new Product();
			product.setTpId(rs.getInt("tp_id"));
			product.setTpName(rs.getString("tp_name"));
			product.setTpDescription(rs.getString("tp_description"));
			product.setTpPrice(rs.getFloat("tp_price"));
			product.setTpStock(rs.getInt("tp_stock"));
			product.setTpcId(rs.getInt("tpc_id"));
			product.setTpcChildId(rs.getInt("tpc_child_id"));
			product.setTpFileName(rs.getString("tp_file_name"));
			list.add(product);

		}
		db.closeAll(null,ps,rs);
		db.closeConn();
		System.out.println("用户足迹记录："+list.size());
		return list;
	}

	@Override
	public List<Product> getHotRecommend(String uId) throws SQLException {
		// TODO Auto-generated method stub
		DBI db = new DBImpl();
		String sql="";
		if("".equals(uId)){
			//无登录态热门商品推荐
			sql="select * from taotaobuy_product where  tpc_child_id in\n" +
					"(select tpc_id from \n" +
					"(select tpc_id,count(tpc_id) from taotaobuy_userfoot \n" +
					"group by tpc_id order by count(tpc_id) desc limit 2) as t) order by rand() limit 2";
		}else{
			//根据用户历史足迹热门商品推荐
			sql="select * from taotaobuy_product where  tpc_child_id in\n" +
					"(select tpc_id from \n" +
					"(select tpc_id,count(tpc_id) from taotaobuy_userfoot where to_user_id=?\n" +
					"group by tpc_id order by count(tpc_id) desc limit 2) as t) order by rand() limit 2";
		}

		Connection connection = db.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = connection.prepareStatement(sql);
		if(!"".equals(uId)){
			ps.setObject(1,uId);
		}
		rs= ps.executeQuery();
		List<Product> list = new ArrayList<Product>();
		while(rs.next()){
			Product product = new Product();
			product.setTpId(rs.getInt("tp_id"));
			product.setTpName(rs.getString("tp_name"));
			product.setTpDescription(rs.getString("tp_description"));
			product.setTpPrice(rs.getFloat("tp_price"));
			product.setTpStock(rs.getInt("tp_stock"));
			product.setTpcId(rs.getInt("tpc_id"));
			product.setTpcChildId(rs.getInt("tpc_child_id"));
			product.setTpFileName(rs.getString("tp_file_name"));
			list.add(product);

		}
		db.closeAll(null,ps,rs);
		db.closeConn();
		System.out.println("获得热门推荐记录"+list.size());
		return list;
	}

	@Override
	public Product getproduct(int pid) throws SQLException {
		// TODO Auto-generated method stub
		DBI db = new DBImpl();
		String sql="select * from taotaobuy_product where tp_id=?";
		Connection connection = db.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = connection.prepareStatement(sql);
		ps.setObject(1,pid);
		rs= ps.executeQuery();
		List<Product> list = new ArrayList<Product>();
		while(rs.next()){
			Product product = new Product();
			product.setTpId(rs.getInt("tp_id"));
			product.setTpName(rs.getString("tp_name"));
			product.setTpDescription(rs.getString("tp_description"));
			product.setTpPrice(rs.getFloat("tp_price"));
			product.setTpStock(rs.getInt("tp_stock"));
			product.setTpcId(rs.getInt("tpc_id"));
			product.setTpcChildId(rs.getInt("tpc_child_id"));
			product.setTpFileName(rs.getString("tp_file_name"));
			list.add(product);

		}
		db.closeAll(null,ps,rs);
		db.closeConn();
		return list!=null&&list.size()>0?list.get(0):null;
	}

}
